1. Field of the Invention
The present invention relates to systems and methods for data querying.
2. Description of the Related Art
Relational database management systems (RDBMS) have become the repository of choice for data, owing to the ease with which data can be extracted in response to a particular user-generated query. The query language typically used in extracting data from RDBMS is SQL, the properties of which advantageously simplify the data retrieval. Unfortunately, SQL is a language that remains beyond the expertise of most users.
A query paradigm referred to as query by example, or QBE, has been developed to address the above-noted problem, wherein a user can indicate an example of a desired query result and an SQL query is then generated based on the user""s indication. Unfortunately, QBE still requires that the user understand the formulation of some query predicates, and further QBE does not provide any indication of the values stored in the database tables. Moreover, when more than one table is required to respond to a query, QBE imposes additional complexity on the user, requiring the user to understand certain join principles of SQL. In light of the above-noted problems, the present invention critically observes that it is desirable to provide a way for user to query a database, including queries against more than a single table, without knowing anything about SQL.
Another query tool has been introduced by Microsoft Corp. under the trade name xe2x80x9cAccessxe2x80x9d. In xe2x80x9cAccessxe2x80x9d, a graphical representation of a query is presented. However, the graphical representation is bound to the structure of queries, not to the categorization of data, which the present invention understands to be a completely different and far more intuitive way to facilitate database querying. Accordingly, the present invention, having understood the need for a query tool that does not require any knowledge of SQL and that is easy and intuitive to use, presents the solutions set forth herein.
A general purpose computer is programmed according to the inventive steps herein to undertake the query by category logic of the present invention. The invention can also be embodied as an article of manufacturexe2x80x94a machine componentxe2x80x94that is used by a digital processing apparatus and which tangibly embodies a program of instructions that are executable by the digital processing apparatus to execute the present logic. This invention is realized in a critical machine component that causes a digital processing apparatus to perform the inventive method steps herein.
Accordingly, a general purpose computer includes logic for querying a database for data. The logic includes presenting, to a user, a category hierarchy representative of data in a relational database system. The logic also includes permitting a user to make a selection of an entry in the category hierarchy. Using the selection, a query is automatically generated.
In a preferred embodiment, the logic includes initializing a node xe2x80x9cCxe2x80x9d based on the selection, and then associating the node xe2x80x9cCxe2x80x9d with a type of node. A predicate based on the type of node is then generated.
In more detail, the logic determines whether the node xe2x80x9cCxe2x80x9d is a category node. If it is, the logic determines whether a value of the node is a range of values, and if so, the logic generates a query predicate based on an upper bound of the range and a lower bound of the range. Otherwise, the logic generates a query predicate based on a single value. If, on the other hand, xe2x80x9cCxe2x80x9d is a single-valued relationship, a query predicate is generated based on a table having the primary key and a table having the foreign key, and the table associated with the primary key is added to a list of tables. Still further, when xe2x80x9cCxe2x80x9d is a multi-valued relationship, a query predicate is generated based on a table having the primary key and a table having the foreign key, and an exist subquery containing the predicate and all previously generated predicates is generated, with the table associated with the foreign table key being added to the list of tables. Or, xe2x80x9cCxe2x80x9d might represent a table node, in which case the logic conditionally adds the node to a list xe2x80x9cTxe2x80x9d of tables.
Once the query predicates have been generated, xe2x80x9cCxe2x80x9d is reset to be its own parent node, and unless the reset xe2x80x9cCxe2x80x9d is one level below the hierarchy root, the logic iteratively generates a predicate based on the type of node as discussed above. At the conclusion of iterations, if any, a xe2x80x9cfromxe2x80x9d query clause is generated by iterating through the list xe2x80x9cTxe2x80x9d of tables, and then a xe2x80x9cselectxe2x80x9d clause is generated.
If desired, a user can be permitted to combine at least two categories in the hierarchy. Also, the logic can include permitting a user to restructure the hierarchy.
In another aspect, a computer-implemented method for querying a database includes selecting a category from a hierarchy representing a relational database, and generating a query using the category.
In still another aspect, a computer program device includes a computer program storage device that can be read by a digital processing apparatus. A program is on the program storage device, and the program includes instructions that are executable by the digital processing apparatus for performing method acts for generating a query based on a user selection of a database category in a hierarchy. The program includes computer readable code means for generating at least one SQL query predicate based on a user-selected category of data in a database.
The details of the present invention, both as to its structure and operation, can best be understood in reference to the accompanying drawings, in which like reference numerals refer to like parts, and in which: